# coding: utf-8
"""
将获取到的db文件转换为xlsx文件
"""
import sqlite3

import pandas as pd

from wechatarticles.utils import base_columns, mode_columns

"""
1: 阅读点赞
2: 评论
3: 正文内容
4: 阅读点赞+评论
5: 阅读点赞+正文内容
6: 评论+正文内容
7: 阅读点赞+评论+正文内容
"""
# 3_json2sqlite.py 里面的dbname
dbname = "dbname"
need_columns = base_columns + mode_columns[4]

if __name__ == "__main__":

    all_columns = base_columns + mode_columns[7]
    conn = sqlite3.connect(dbname + ".db")

    table_lst = conn.execute(
        'select * from sqlite_master where type="table";'
    ).fetchall()
    table_name_lst = [item[1] for item in table_lst]
    table_data_lst = []
    for table_name in table_name_lst:
        table_res = conn.execute("SELECT * FROM '{}'".format(table_name))
        data = table_res.fetchall()

        df = pd.DataFrame(data, columns=all_columns)
        for c in df.columns:
            if c not in need_columns:
                del df[c]
        df.to_excel(table_name + ".xlsx", encoding="utf-8", index=False)
